﻿using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using ProfileManager.Helpers;

namespace ProfileManager.DataAccess
{
    public class CommonDb
    {
        private const string SqlParseRegex = "^\\s*GO\\s*$";

        public static SqlConnection GetBaseConection()
        {
            return new SqlConnection(ConfigReader.OilWizardConectionString);
        }

        public static SqlConnection GetDefaultServerConection()
        {
            return new SqlConnection(ConfigReader.DefaultServerString);
        }

        private static void CheckDataBaseServer()
        {
            SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
            DataTable table = instance.GetDataSources();

            DataRow[] rows = table.Select("Version LIKE '9%'");
            foreach (DataRow row in rows)
            {
                ShowMessage.Info(row["ServerName"].ToString());
            }
        }

        public static bool CheckDataBase()
        {
            try
            {
                using (SqlConnection con = GetDefaultServerConection())
                {
                    SqlCommand cmd = new SqlCommand("select count(*) from sys.databases where name like @DbName", con);
                    cmd.Parameters.Add("@DbName", SqlDbType.NVarChar).Value = ConfigReader.DbName;
                    
                    con.Open();
                    
                    return Convert.ToInt32(cmd.ExecuteScalar()) == 1;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }

        private static bool CheckConnection()
        {
            try
            {
                using (SqlConnection con = GetBaseConection())
                {
                    con.Open();
                    return true;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }

        public static bool CreateDataBase(string pathToFile, string pathToBase)
        {
            try
            {
                return ExecuteScript(FileSystem.ReadDataBaseScriptFromFile(pathToFile, pathToBase));
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }
        
        public static bool CreateFullDatabase(string pathToBase)
        {
            return CreateDataBase(pathToBase) && CreateUserLogin();
        }

        private static bool CreateDataBase(string pathToBase)
        {
            try
            {
                return ExecuteScript(FileSystem.ReadDataBaseScriptFromResources(pathToBase));
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }

        private static bool CreateUserLogin()
        {
            try
            {
                return ExecuteScript(FileSystem.ReadUserScriptFromResources());
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }

        private static bool ExecuteScript(string sqlBaseContent)
        {
            try
            {
                string[] sqlBaseContentParts = Regex.Split(sqlBaseContent, SqlParseRegex, RegexOptions.Multiline);

                using (SqlConnection con = GetDefaultServerConection())
                {
                    con.Open();

                    for (int i = 0; i < sqlBaseContentParts.Length; i++)
                    {
                        string sqlCmd = sqlBaseContentParts[i];
                        if (!string.IsNullOrEmpty(sqlCmd.Trim()))
                        {
                            SqlCommand createCmd = new SqlCommand(sqlCmd.Trim(), con);
                            createCmd.ExecuteNonQuery();
                        }
                    }

                    con.Close();

                    return true;
                }
            }
            catch (Exception ex)
            {
                Logger.WriteLog(ex);
                return false;
            }
        }
    }
}
